import pymysql
import datetime


class MYSQL():
    def __init__(self):
        self.db = pymysql.connect(host="127.0.0.1", port=8080, passwd="2503aabb", db="qi")
        # 2503aabb是我的数据库密码，qi是我创建的数据库名
        self.cursor = self.db.cursor()

    def insert_with_pic(self, uptime, Scal, SDC, SMS, CMS, pic):
        if len(pic) < 256:  # 这里数据库pic字段使用的是char类型，暂时接受长度255以下
            sql = 'insert into test1(upload_time, Scalpel, Straight_Dissection_Clamp, ' \
                  'Straight_Mayo_Scissor, Curved_Mayo_Scissor, last_update, pic) values ' \
                  '(\"'+uptime+'\",\"'+str(Scal)+'\",\"'+str(SDC)+'\",\"'+str(SMS)+'\",\"'+str(CMS)+'\",\"'+time+'\",\"'+pic+'\")'
            self.cursor.execute(sql)
            self.db.commit()
            # 如果插入数字，需要转为字符型：str(case_id);
            # 如果插入字符，需要使用单引号来表示字符：‘" + operator + "‘；
            # 如果插入日期格式，需要先转为字符，然后再单引号：
        else:
            self.insert_no_pic(uptime, Scal, SDC, SMS, CMS)
        
    def insert_no_pic(self, uptime, Scal, SDC, SMS, CMS):
        sql = 'insert into test1(upload_time, Scalpel, Straight_Dissection_Clamp, ' \
              'Straight_Mayo_Scissor, Curved_Mayo_Scissor, last_update) values ' \
              '(\"'+uptime+'\",\"'+str(Scal)+'\",\"'+str(SDC)+'\",\"'+str(SMS)+'\",\"'+str(CMS)+'\",\"'+time+'\")'
        self.cursor.execute(sql)
        self.db.commit()
        
    def select_sql(self, num = 5):
        # 调出最近 num 条历史记录
        # num=0 ： 调出全部历史记录
        # 不输入则默认调出最近5条记录
        if num == 0:
            sql = 'SELECT * FROM test1 ORDER BY id DESC'
            
        else:
            sql = 'SELECT * FROM test1 ORDER BY id DESC LIMIT ' + str(num)
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        for i in range(len(result)-1, -1, -1):
            print(result[i])
        
    def sql_close(self):
        self.db.close()
# cursor.execute("drop table if exists fin;") #如果存在fin表时候先删除
# sql = '''create table user1
# 	(
#
# 	);'''
# cursor.execute(sql)
# # 以上是建表操作（备用）

if __name__ == "__main__":
    # 测试调用效果
    db = MYSQL()
    time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    # db.insert_with_pic(time,1,2,3,4,'i'*299)
    db.select_sql()
    db.sql_close()
